package com.yan.base;

import org.junit.Test;

import java.sql.*;

public class JDBCOperation {
    @Test
    public void testQuerySingleRowAndCol()  throws Exception{
        // 1.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stguigu", "root", "123456");
        // 2.预编译
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(*) FROM t_emp");
        // 3.执行sql
        ResultSet resultSet = preparedStatement.executeQuery();
        // 4.处理结果
        if (resultSet.next()){
            System.out.println(resultSet.getInt(1));
        }
        // 5.释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testQuerySingleRow() throws Exception{
        // 1.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stguigu", "root", "123456");
        // 2.预编译
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT emp_id, emp_name,emp_salary,emp_age FROM t_emp WHERE emp_id = ?");
        // 3.执行sql
        preparedStatement.setInt(1, 5);
        ResultSet resultSet = preparedStatement.executeQuery();
        // 4.处理结果
        if (resultSet.next()){
            System.out.println(resultSet.getInt("emp_id"));
            System.out.println(resultSet.getString("emp_name"));
            System.out.println(resultSet.getDouble("emp_salary"));
            System.out.println(resultSet.getInt("emp_age"));
        }
        // 5.释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testQueryMultipleRowsAndCols() throws Exception{
        // 1.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stguigu", "root", "123456");
        // 2.预编译
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT emp_id, emp_name,emp_salary,emp_age FROM t_emp WHERE emp_age > ?");
        // 3.执行sql
        preparedStatement.setInt(1, 5);
        ResultSet resultSet = preparedStatement.executeQuery();
        // 4.处理结果
        while (resultSet.next()){
            System.out.println(resultSet.getInt("emp_id"));
            System.out.println(resultSet.getString("emp_name"));
            System.out.println(resultSet.getDouble("emp_salary"));
            System.out.println(resultSet.getInt("emp_age"));
        }
        // 5.释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testInsert() throws Exception{
        // 1.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stguigu", "root", "123456");
        // 2.预编译
        PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO t_emp(emp_name,emp_salary,emp_age) VALUES(?,?,?)");
        // 3.执行sql
        preparedStatement.setString(1, "rose");
        preparedStatement.setDouble(2, 100);
        preparedStatement.setInt(3, 28);
        int resultSet = preparedStatement.executeUpdate();
        // 4.处理结果
        if(resultSet > 0){
            System.out.println("插入成功");
        }
        else {
            System.out.println("插入失败");
        }
        // 5.释放资源
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testUpdate() throws Exception{
        // 1.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stguigu", "root", "123456");
        // 2.预编译
        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE t_emp SET emp_salary= ? WHERE emp_id = ?");
        // 3.执行sql
        preparedStatement.setDouble(1, 888.88);
        preparedStatement.setInt(2, 6);
        int resultSet = preparedStatement.executeUpdate();
        // 4.处理结果
        if(resultSet > 0){
            System.out.println("修改成功");
        }
        else {
            System.out.println("修改失败");
        }
        // 5.释放资源
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testDelete()throws Exception{
        // 1.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/stguigu", "root", "123456");
        // 2.预编译
        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM t_emp WHERE emp_id = ?");
        // 3.执行sql
        preparedStatement.setInt(1, 6);
        int resultSet = preparedStatement.executeUpdate();
        // 4.处理结果
        if(resultSet > 0){
            System.out.println("删除成功");
        }
        else {
            System.out.println("删除失败");
        }
        // 5.释放资源
        preparedStatement.close();
        connection.close();
    }
}
